import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;


public class BudgetModel {

	/**
	 * Gets the finanes for a club
	 * @param clubid Club to retreive finances for
	 * @return       Array of finance data
	 */
	public static Finance[] getFinances( int clubid ){
		Finance[] finances = null;
		ResultSet rs = DB.Query("SELECT transaction_date, description, location, amount FROM Finances WHERE club_id="+clubid+";");
		
		if( rs != null ){
			try{
				// Init array
				rs.last();
				finances = new Finance[rs.getRow()];
				rs.first();
				
				// Fill array
				for( int i = 0; i < finances.length; ++i, rs.next() ){
					Finance f = new Finance();
					f.date = rs.getDate("transaction_date");
					f.description = rs.getString("description");
					f.location = rs.getString("location");
					f.amount = rs.getBigDecimal("amount");
					finances[i] = f;
				}
			}catch( SQLException e ){
				// SQL Error, return null
				System.err.println( e.getMessage() );
				finances = null;
			}
		}
		
		return finances;
	}
	
	public static Budget getBudget
		(int clubid, int quarterid)
		
	{
		try{
	//Statement sm = cn.createStatement();
	ResultSet rs = DB.Query("SELECT * FROM Budget WHERE club_id="+clubid+" and quarter_id="+quarterid+";");
	if(rs != null)
	{
		rs.first();
		Budget budget = new Budget();
		budget.available = rs.getBigDecimal("available");
		budget.used = rs.getBigDecimal("used");
		return budget;
	}
	else
	{
		return null;
	}
		}
		catch(SQLException e)
		{
			return null;
		}
	}
	
	public static boolean addBudget
		(int club_id, int quarter_id, BigDecimal available, BigDecimal used)
	{
		String query = "INSERT INTO Budget (`club_id`, `quarter_id` , `available`, `used`) VALUES ('" + club_id + "', '" + quarter_id + "', '" + available + "', '" + used + "');";
		return DB.Update(query);
	}
	
	public static boolean addFinance
	(int clubid, int quarterid, Date date, String desc, String loc, BigDecimal amount )
	throws FinanceException
{
	String[] queries = new String[3];
	queries[0] = "SELECT * FROM Budget WHERE club_id="+clubid+" and quarter_id="+quarterid+" and ( available + " + amount + " ) >= 0;";
	queries[1] = "INSERT INTO Finances (`club_id`, `transaction_date`, `description`, `location`, `amount`) VALUES ('" + clubid + "', '" + date + "', '" + desc + "', '" + loc + "', '" + amount + "');";
	queries[2] = "Update Budget SET used = (used-" + amount +  "), available = (available +" + amount +  ") WHERE club_id="+clubid+" and quarter_id="+quarterid+";";
	return DB.Transaction(queries);
	
}
	
	public static boolean addQuarter
	(int quarter_id, Date start_date, Date end_date)
{
	String query = "INSERT INTO Quarter (`quarter_id`, `start_date`, `end_date`) VALUES ('" + quarter_id + "', '" + start_date + "', '" + end_date + "');";
	return DB.Update(query);
}

public static Integer[] getQuarters
	()
{
	try{
		String query = "SELECT quarter_id FROM Quarter";
		ResultSet rs = DB.Query(query);
		if(rs != null)
		{
			rs.last();
			Integer[] quarters = new Integer[rs.getRow()];
			rs.first();
			do
			{
				quarters[rs.getRow() - 1] = rs.getInt("quarter_id");
			}
			while(rs.next());
			return quarters;
		}
		else
		{
			return new Integer[0];
		}
	}
	catch(SQLException e)
	{
		return null;
	}
}
	
	//testing
	public static void main(String args[])
	{
		try
		{
			DB.Initialize();
		    boolean worked = BudgetModel.addFinance(1, 20101, new Date(1234567890), "whatever", "Somewhere", new BigDecimal("-100.00"));
		    System.out.println(worked);
		}
		catch(DBError e)
		{
			System.err.print("DBERROR");
		}
		catch(FinanceException e)
		{
			System.err.println(e.getMessage());
		}
	}
}

